package jp.co.nes.awssupport.access.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.time.YearMonth;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import jp.co.nes.awssupport.common.base.CommonException;
import jp.co.nes.awssupport.common.bean.MonthlyCompiledBillingBean;
import jp.co.nes.awssupport.common.dao.BaseJdbcDao;
import jp.co.nes.awssupport.common.dao.BeanProcessorEx;
import jp.co.nes.awssupport.common.dao.ReadType;
import jp.co.nes.awssupport.common.utility.DateUtil;
import jp.co.nes.awssupport.common.utility.DbConnUtil;

/**
 * 月次集計結果DAOです。
 */
public class MonthlyCompiledBillingDao extends BaseJdbcDao<MonthlyCompiledBillingBean> {

    /** 月次集計結果を検索する際に使用するSelect文(ご利用明細用) */
    private static final String SEL_FOR_USAGE_REPORT = ""
            + " select LinkedAccountId,"
            + "        YearMonth,"
            + "        ProductName,"
            + "        UsageType,"
            + "        TRUNCATE(Rate + 0.000000005, 8) as Rate,"
            + "        TRUNCATE(UsageQuantity + 0.000000005, 8) as UsageQuantity,"
            + "        TRUNCATE(BillingAmount + 0.0005, 3) as BillingAmount,"
            + "        Description,"
            + "        Unit"
            + "   from monthly_compiled_billing"
            + " where LinkedAccountId = ?"
            + "   and YearMonth = ?"
            + " order by ProductName, Description";

    /** 月次集計結果を登録する際のInsert文 */
    private static final String BATCH_INS = ""
            + "insert into monthly_compiled_billing ("
            + "    LinkedAccountId,"
            + "    YearMonth,"
            + "    ProductName,"
            + "    UsageType,"
            + "    Rate,"
            + "    UsageQuantity,"
            + "    BillingAmount,"
            + "    Description,"
            + "    Unit)"
            + " values (?, ?, ?, ?, ?, ?, ?, ?, ?)";

    /** 月次集計結果を削除する際のDelete文 */
    private static final String DEL_LINKED_ACCOUNT_ID_YEAR_MONTH = ""
            + "delete from monthly_compiled_billing"
            + " where LinkedAccountId = ?"
            + "   and YearMonth = ?";

    /**
     * 月次集計結果を読み込む際の区分です。
     */
    public enum DaoReadTypeOfTotalization implements ReadType {

        /** isProductName=false, isUsageType=false -> 「連結アカウントID, 年月」単位で月次集計結果を読み込みます。 */
        TOTALIZATION_FF(false, false),

        /** isProductName=true,  isUsageType=false -> 「連結アカウントID, 年月, 製品名」単位で月次集計結果を読み込みます。 */
        TOTALIZATION_TF(true, false),

        /** isProductName=false, isUsageType=true  -> 「連結アカウントID, 年月, 使用タイプ」単位で月次集計結果を読み込みます。 */
        TOTALIZATION_FT(false, true),

        /** isProductName=true,  isUsageType=true  -> 「連結アカウントID, 年月, 製品名, 使用タイプ」単位で月次集計結果を読み込みます。 */
        TOTALIZATION_TT(true, true);

        /** 製品名フラグ(true: 製品名をgroup byで使用します、false: 製品名をgroup byで使用しません) */
        private boolean isProductName = false;

        /** 使用タイプフラグ(true: 使用タイプをgroup byで使用します、false: 使用タイプをgroup byで使用しません) */
        private boolean isUsageType = false;

        /**
         * Select文を引数にもつコンストラクタです。
         *
         * @param select Select文
         */
        private DaoReadTypeOfTotalization(boolean isProductName, boolean isUsageType) {
            this.isProductName = isProductName;
            this.isUsageType = isUsageType;
        }

        /*
         * (非 Javadoc)
         * @see jp.co.nes.awssupport.common.constant.ReadType#getSelect()
         */
        @Override
        public String getSelect() {
            StringBuilder builder = new StringBuilder();
            builder.append("select LinkedAccountId, ");
            builder.append("YearMonth, ");
            if (isProductName) {
                builder.append("ProductName, ");
            } else {
                builder.append("null as ProductName, ");
            }
            if (isUsageType) {
                builder.append("UsageType, ");
            } else {
                builder.append("null as UsageType, ");
            }
            builder.append("null as Rate, null as UsageQuantity, ");
            builder.append("TRUNCATE(sum(TRUNCATE(BillingAmount + 0.0005, 3)) + 0.5, 0) as BillingAmount, ");
            builder.append("null as Description, null as Unit ");
            builder.append("from monthly_compiled_billing ");
            builder.append("where LinkedAccountId = ? ");
            builder.append("and YearMonth = ? ");
            builder.append("group by LinkedAccountId, ");
            if (isProductName && isUsageType) {
                builder.append("YearMonth, ");
                builder.append("ProductName, ");
                builder.append("UsageType");
            } else if (isProductName && !isUsageType) {
                builder.append("YearMonth, ");
                builder.append("ProductName");
            } else if (!isProductName && isUsageType) {
                builder.append("YearMonth, ");
                builder.append("UsageType");
            } else {
                // !isProductName && !isUsageTypeの場合
                builder.append("YearMonth");
            }
            return builder.toString();
        }
    }

    /**
     * デフォルトコンストラクタです。
     */
    public MonthlyCompiledBillingDao() {
    }

    /**
     * 月次集計結果リスト(ご利用明細用)を返却します。<br>
     * 使用合計量、適用レートは小数第9位で四捨五入された結果が返却されます。
     * また、請求額（税抜）は、小数第4位で四捨五入された結果が返却されます。
     *
     * @param linkedAccountId 連結アカウントID
     * @param yearMonth 年月
     * @return 月次集計結果リスト(ご利用明細用)
     */
    public List<MonthlyCompiledBillingBean> getListForUsageReport(String linkedAccountId, YearMonth yearMonth) {
        try {
            Connection connection = DbConnUtil.getConn();

            List<Object> paramList = new ArrayList<>();
            paramList.add(linkedAccountId);
            paramList.add(DateUtil.toInt(yearMonth));
            Object[] params = paramList.toArray();

            QueryRunner runner = new QueryRunner();
            return runner.query(connection, SEL_FOR_USAGE_REPORT,
                    new BeanListHandler<>(MonthlyCompiledBillingBean.class,
                            new BasicRowProcessor(new BeanProcessorEx())),
                    params);
        } catch (SQLException e) {
            throw new CommonException(e);
        }
    }

    /**
     * 引数の連結アカウントID,年月に該当する月次集計結果を削除します。
     *
     * @param linkedAccountId 連結アカウントID
     * @param yearMonth 年月
     */
    public void delete(String linkedAccountId, YearMonth yearMonth) {
        try {
            Connection connection = DbConnUtil.getConn();

            Object[] params = new Object[2];
            params[0] = linkedAccountId;
            params[1] = DateUtil.toInt(yearMonth);

            QueryRunner runner = new QueryRunner();
            runner.update(connection, DEL_LINKED_ACCOUNT_ID_YEAR_MONTH, params);
        } catch (SQLException e) {
            throw new CommonException(e);
        }
    }

    /*
     * (非 Javadoc)
     * @see jp.co.nes.awssupport.common.dao.BaseJdbcDao#getBatchInsQuery()
     */
    @Override
    protected String getBatchInsQuery() {
        return BATCH_INS;
    }

    /*
     * (非 Javadoc)
     * @see jp.co.nes.awssupport.common.dao.BaseJdbcDao#toConditionList(jp.co.nes.awssupport.common.constant.ReadType, java.lang.Object[])
     */
    @Override
    protected List<Object> toConditionList(ReadType readType, Object... conditions) {
        List<Object> conditionList = new ArrayList<>();
        if (readType == DaoReadTypeOfTotalization.TOTALIZATION_FF
                || readType == DaoReadTypeOfTotalization.TOTALIZATION_TF
                || readType == DaoReadTypeOfTotalization.TOTALIZATION_FT
                || readType == DaoReadTypeOfTotalization.TOTALIZATION_TT) {
            conditionList.add(conditions[0]);
            conditionList.add(conditions[1]);
        } else {
            throw new CommonException("ReadType is wrong");
        }
        return conditionList;
    }

    /*
     * (非 Javadoc)
     * @see jp.co.nes.awssupport.common.dao.BaseJdbcDao#toBatchInsObjectList(jp.co.nes.awssupport.common.bean.BaseBean)
     */
    @Override
    protected List<Object> toBatchInsObjectList(MonthlyCompiledBillingBean bean) {
        List<Object> objectList = new ArrayList<>();
        objectList.add(bean.getLinkedAccountId());
        objectList.add(bean.getYearMonth());
        objectList.add(bean.getProductName());
        objectList.add(bean.getUsageType());
        objectList.add(bean.getRate());
        objectList.add(bean.getUsageQuantity());
        objectList.add(bean.getBillingAmount());
        objectList.add(bean.getDescription());
        objectList.add(bean.getUnit());
        return objectList;
    }
}
